﻿using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Linq;
using System.Text;

namespace CreateDatabase
{
    class Program
    {

        static void Main(string[] args)
        {
            using (SQLiteConnection connection = new SQLiteConnection(@"Data Source=..\..\..\Zimmerreservierung\db.dat"))
            {
                connection.Open();
                CreateKundenTable(connection);
                CreateZimmerTable(connection);
                CreateRechnung(connection);                
                CreateReservierung(connection);
                connection.Close();
            }

            Console.WriteLine("Press any key...");
            Console.ReadLine();
        }

        private static void CreateRechnung(SQLiteConnection connection)
        {
            using (SQLiteCommand command = new SQLiteCommand(connection))
            {
                command.CommandText = "DROP TABLE IF EXISTS Rechnung";
                command.ExecuteNonQuery();
                Console.WriteLine("Dropped Table Rechnung");

                command.CommandText = "CREATE TABLE IF NOT EXISTS Rechnung (" +
                    "ID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, " +
                    "KundenId INTEGER NOT NULL, " +
                    "Gesamtpreis REAL, " +
                    "Datum VARCHAR(20), " +
                    "FOREIGN KEY(KundenId) REFERENCES Kunde(ID) );";
                int i = command.ExecuteNonQuery();
                Console.WriteLine("Table Rechnung created: " + i.ToString());


            }
        }

        private static void CreateReservierung(SQLiteConnection connection)
        {
            using (SQLiteCommand command = new SQLiteCommand(connection))
            {
                command.CommandText = "DROP TABLE IF EXISTS Reservierung";
                command.ExecuteNonQuery();
                Console.WriteLine("Dropped Table Reservierung");

                command.CommandText = "CREATE TABLE IF NOT EXISTS Reservierung (" + 
                    "ID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, " + 
                    "KundenId INTEGER NOT NULL, " +
                    "ZimmerId INTEGER NOT NULL, " +
                    "RechnungsId INTEGER, " +
                    "Rabatt REAL, " + 
                    "Zimmerpreis REAL NOT NULL, " +
                    "Zimmerpreisart VARCHAR(10) NOT NULL, " + 
                    "Storniert INTEGER, " +
                    "Startdatum DATETIME NOT NULL, " +
                    "Enddatum DATETIME NOT NULL, " +
                    "FruehzeitigesEnddatum DATETIME, " +
                    "FOREIGN KEY(KundenId) REFERENCES Kunde(ID), " +
                    "FOREIGN KEY(ZimmerId) REFERENCES Zimmer(ID), " + 
                    "FOREIGN KEY(RechnungsId) REFERENCES Rechnung(ID) );";
                int i = command.ExecuteNonQuery();
                Console.WriteLine("Table Reservierung created: " + i.ToString());



                command.CommandText = "INSERT INTO Reservierung (ID, KundenId, ZimmerId, RechnungsId, Rabatt, Zimmerpreis, Zimmerpreisart, Storniert, Startdatum, Enddatum, FruehzeitigesEnddatum ) "
                                                + "VALUES (NULL, 1, 1, NULL, NULL, '1000.0', '1 EW',  0, '2013-10-01 14:00:00.000', '2013-10-05 10:00:00.000', NULL );";
                command.ExecuteNonQuery();
                Console.WriteLine("Inserted Row in Table Reservierung");

                command.CommandText = "INSERT INTO Reservierung (ID, KundenId, ZimmerId, RechnungsId, Rabatt, Zimmerpreis, Zimmerpreisart, Storniert, Startdatum, Enddatum, FruehzeitigesEnddatum ) "
                                               + "VALUES (NULL, 2, 2, NULL, NULL, '1000.0', '1 EW', 1, '2013-10-01 14:00:00.000', '2013-10-05 10:00:00.000', NULL );";
                command.ExecuteNonQuery();
                Console.WriteLine("Inserted Row in Table Reservierung");

                //command.CommandText = "INSERT INTO Zimmer (ID, Name, MaxBelegzahl, PreisEZ, PreisDZ, PreisDreifachZ, PreisEZmitKind, PreisEZmit2Kind, PreisDZmitKind ) "
                //                                 + "VALUES (NULL, 'Family Suite', 4, '700.00', '800.00', '750.00', '790.00', '795.00', '800.00' );";
                //command.ExecuteNonQuery();
                //Console.WriteLine("Inserted Row in Table Zimmer");
            }
           
        }

        private static void CreateZimmerTable(SQLiteConnection connection)
        {
            using (SQLiteCommand command = new SQLiteCommand(connection))
            {
                command.CommandText = "DROP TABLE IF EXISTS Zimmer";
                command.ExecuteNonQuery();
                Console.WriteLine("Dropped Table Zimmer");

                command.CommandText = "CREATE TABLE IF NOT EXISTS Zimmer ( ID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, Name VARCHAR(100) NOT NULL, MaxBelegzahl INTEGER NOT NULL, "
                                                                        + " PreisEZ REAL NOT NULL, PreisDZ REAL, PreisDreifachZ REAL, PreisEZmitKind REAL, PreisEZmit2Kind REAL, PreisDZmitKind REAL );";
                int i = command.ExecuteNonQuery();
                Console.WriteLine("Table Zimmer created: " + i.ToString());
                
                
                command.CommandText = "INSERT INTO Zimmer (ID, Name, MaxBelegzahl, PreisEZ, PreisDZ, PreisDreifachZ, PreisEZmitKind, PreisEZmit2Kind, PreisDZmitKind ) "
                                                + "VALUES (NULL, 'HoneyMoon Suite', 2, '1500.00', '1700.00', '', '', '', '' );";
                command.ExecuteNonQuery();
                Console.WriteLine("Inserted Row in Table Zimmer");

                command.CommandText = "INSERT INTO Zimmer (ID, Name, MaxBelegzahl, PreisEZ, PreisDZ, PreisDreifachZ, PreisEZmitKind, PreisEZmit2Kind, PreisDZmitKind ) "
                                                 + "VALUES (NULL, 'Family Suite', 4, '700.00', '800.00', '750.00', '790.00', '795.00', '800.00' );";
                command.ExecuteNonQuery();
                Console.WriteLine("Inserted Row in Table Zimmer");

                command.CommandText = "INSERT INTO Zimmer (ID, Name, MaxBelegzahl, PreisEZ, PreisDZ, PreisDreifachZ, PreisEZmitKind, PreisEZmit2Kind, PreisDZmitKind ) "
                                                 + "VALUES (NULL, 'Presidential Suite', 4, '900.00', '1000.00', '850.00', '890.00', '995.00', '800.00' );";
                command.ExecuteNonQuery();
                Console.WriteLine("Inserted Row in Table Zimmer");
            }
        }

        static void CreateKundenTable(SQLiteConnection connection)
        {
            using (SQLiteCommand command = new SQLiteCommand(connection))
            {
                command.CommandText = "DROP TABLE IF EXISTS Kunde";
                command.ExecuteNonQuery();
                Console.WriteLine("Dropped Table Kunde");

                command.CommandText = "CREATE TABLE IF NOT EXISTS Kunde ( ID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, Name VARCHAR(100) NOT NULL, Rechnungsaddresse VARCHAR(100) NOT NULL, "
                                                                        + " Firmenname VARCHAR(100), Rabatt REAL, Telefonnummer VARCHAR(100) NOT NULL, Faxnummer VARCHAR(100), Email VARCHAR(100), "
                                                                        + " Web VARCHAR(100), Notiz TEXT );";
                command.ExecuteNonQuery();
                Console.WriteLine("Table Kunde created");

                command.CommandText = "INSERT INTO Kunde ( ID, Name, Rechnungsaddresse, Firmenname, Rabatt, Telefonnummer, Faxnummer, Email, Web, Notiz ) "
                                                + "VALUES (NULL, 'Andreas TU', 'Karlsplatz 13, 1040 Wien', 'Technische Universität Wien', '0.02', "
                                                            + "'+43-1-58801-0', '+43-1-58801-41088', 'webmaster@tuwien.ac.at', 'http://www.tuwien.ac.at/',  '');";
                command.ExecuteNonQuery();
                Console.WriteLine("Inserted Row in Table Kunde");

                command.CommandText = "INSERT INTO Kunde ( ID, Name, Rechnungsaddresse, Firmenname, Rabatt, Telefonnummer, Faxnummer, Email, Web, Notiz ) "
                                                + "VALUES (NULL, 'Otto IBM', 'Obere Donaustraße 95, 1020 Wien', 'IBM', '0.04', "
                                                            + "'+43-1-211 45-0 ', '+43-1-216 08 86', 'gsc@at.ibm.com', 'http://www.ibm.at', '' );";
                command.ExecuteNonQuery();
                Console.WriteLine("Inserted Row in Table Kunde");
            }
        }
    }
}
